package com.pacifictextiles.qc.inspection.server.model.dao.impl;

//记得添加 ORDER BY `dateAndTime` ASC


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.pacifictextiles.qc.inspection.server.model.dao.IBatchDAO;
import com.pacifictextiles.qc.inspection.server.model.dbc.DatabaseConnection;
import com.pacifictextiles.qc.inspection.server.model.vo.Batch;

public class BatchDAOImpl implements IBatchDAO {

	@Override
	public boolean addBatch(Batch batch) throws Exception {

		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		boolean isSuccess = false;

		String insert = "INSERT INTO `qc_inspection`.`batch` "
				+ "(`fabricType`, `dispoNo`, `batchNo`, `repairNo`, `colorCode`,"
				+ " `fabricCode`, `dispoLineNo`, `customerCode`, `fullCode`, `customerPO`,"
				+ " `shipSample`, `shrinkageSample`, `ppSample`, `pieceSample`, `qualityTable`) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		try {

			// 准备好预处理查询语句
			pStatement = conn.prepareStatement(insert);

			// 赋值到查询语句中？号的位置从1到N
			// FarbricType在数据库里设置的是单个char
			pStatement.setString(1, String.valueOf(batch.getFabricType()));
			pStatement.setString(2, batch.getDispoNo());
			pStatement.setString(3, batch.getBatchNo());
			pStatement.setString(4, batch.getRepairNo());
			pStatement.setString(5, batch.getColorCode());
			pStatement.setString(6, batch.getFabricCode());
			pStatement.setString(7, batch.getDispoLineNo());
			pStatement.setString(8, batch.getCustomerCode());
			pStatement.setString(9, batch.getFullCode());
			pStatement.setString(10, batch.getCustomerPO());
			pStatement.setInt(11, batch.getShipSample());
			pStatement.setInt(12, batch.getShrinkageSample());
			pStatement.setInt(13, batch.getPpSample());
			pStatement.setInt(14, batch.getPieceSample());
			pStatement.setString(15, batch.getQualityTable());

			// 执行预处理语句 用 validate 接受返回的 int 判断是否执行成功
			int validate = 0;
			System.out.print("The validation num is:" + validate + "\n");
			validate = pStatement.executeUpdate();
			System.out.print("The validation num is:" + validate + "\n"
					+ "执行通过" + "\n");

			// 判断是否执行成功
			if (validate > 0) {
				System.out.print("BatchDaoImpl:  添加batch成功" + "\n\n");
			}
			
			// 关闭数据库连接
			dbc.close();
			
			//返回 boolean 值判断是否执行成功
			return isSuccess;
			
		} catch (SQLException e) {
			//e.printStackTrace();
			System.out.print("BatchDaoImpl:  请检查插入语句是否正确"+"\n\n");
		}
		
		//返回 boolean 值判断是否执行成功
		return isSuccess;
	}

	@Override
	public boolean deleteBatch(String fullCode) throws Exception {

		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		boolean isSuccess = false;

		String delete = "DELETE FROM `qc_inspection`.`batch` WHERE `batch`.`fullCode` = ? ";

		try {

			// 准备好预处理查询语句
			pStatement = conn.prepareStatement(delete);

			// 赋值到查询语句中？号的位置从1到N
			pStatement.setString(1, fullCode);

			// 执行预处理语句 用 validate 接受返回的 int
			int validate = 0;
			System.out.print("The validation num is:" + validate + "\n");
			validate = pStatement.executeUpdate();
			System.out.print("The validation num is:" + validate + "\n"
					+ "执行通过" + "\n");

			// 判断是否执行成功
			if (validate > 0) {
				System.out.print("BatchDaoImpl:  删除batch成功" + "\n\n");
				isSuccess = true;
			} else {
				System.out.print("BatchDaoImpl:  找不到这一个batch" + "\n\n");
			}

			// 关闭数据库连接
			dbc.close();

			// 返回 boolean 值判断是否执行成功
			return isSuccess;

		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.print("BatchDaoImpl:  请检查删除语句是否正确" + "\n");
		}

		// 返回 boolean 值判断是否执行成功
		return isSuccess;
	}

	@Override
	public Batch getBatch(String fullCode) throws Exception {
		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		ResultSet rs = null;

		if (isFullCodeExist(fullCode)) {
			
			// 记得添加 ORDER BY `dateAndTime` ASC

			// 定义查询语句
			// select预处理语句中，使用?代替变量会报错
			String select = "SELECT *  FROM `batch` WHERE `batch`.`fullCode` = '"+fullCode+"'";

			try {

				// 准备好预处理查询语句
				pStatement = conn.prepareStatement(select);

				rs = pStatement.executeQuery(select);

				if (rs.next()) {

					Batch batch = new Batch();

					// 给传入的batch对象设置set方法
					batch.setFabricType(rs.getString("fabricType").charAt(0));
					batch.setDispoNo(rs.getString("dispoNo"));
					System.out.println(batch.getDispoNo());
					batch.setBatchNo(rs.getString("batchNo"));
					batch.setRepairNo(rs.getString("repairNo"));
					batch.setColorCode(rs.getString("colorCode"));
					batch.setFabricCode(rs.getString("fabricCode"));
					batch.setDispoLineNo(rs.getString("dispoLineNo"));
					batch.setCustomerCode(rs.getString("customerCode"));
					batch.setFullCode(rs.getString("fullCode"));
					batch.setCustomerPO(rs.getString("customerPO"));
					batch.setShipSample(rs.getInt("shipSample"));
					batch.setShrinkageSample(rs.getInt("shrinkageSample"));
					batch.setPieceSample(rs.getInt("ppSample"));
					batch.setPpSample(rs.getInt("pieceSample"));
					batch.setQualityTable(rs.getString("qualityTable"));
					batch.setFabricCorrectionFactor(rs
							.getFloat("fabricCorrectionFactor"));
					batch.setLotNo(rs.getString("lotNo"));

					// 顺利执行后输出提示
					System.out.print("BatchDaoImpl:  获得一缸成功\n");
					dbc.close();
					return batch; } 

			} catch (Exception e) {
				// 抓到exception 给出提示
				//e.printStackTrace();
				System.out.print("BatchDaoImpl:  获得一缸失败\n");
			}

		}// if
		
		return null;
	}
	
	public boolean isFullCodeExist(String fullCode) throws Exception {

		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		ResultSet rs = null;
		boolean isExist = false;
		
		//定义查询语句
		String select = "SELECT *  FROM `batch` WHERE `batch`.`fullCode` = '"+ fullCode +"'";

		try {

			// 准备好预处理查询语句
			pStatement = conn.prepareStatement(select);

			// 执行查询语句
			rs = pStatement.executeQuery(select);

			// 如果result set有下一项
			if (rs.next()) {

				isExist = true;

				System.out.print("BatchDaoImpl:  Checking Existence:    找到缸号" + "\n\n");

			} else {

				System.out.print("BatchDaoImpl:  Checking Existence:    缸号为空" + "\n\n");
			}

		} catch (Exception e) {
			System.out.print("BatchDaoImpl:  执行查询语句失败\n\n");
		}

		return isExist;
	}
}
